From 9481265af1b946a8456c425da9066b094a5620ee Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Sat, 11 Jul 2026 22:38:45 +0000
Subject: [PATCH] Fix so that sdb_arrival EVENTS must have  a FOLLOW_OBSERVERS
 row

---
 db/schemas/lib/triggers/create/events.m4 | 113 +++++++++++++++++++++++
 1 file changed, 113 insertions(+)

diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4
index 429b1fc..c7c3d96 100644
--- a/db/schemas/lib/triggers/create/events.m4
+++ b/db/schemas/lib/triggers/create/events.m4
@@ -210,6 +210,111 @@ CREATE OR REPLACE FUNCTION events_func ()
 $$;
 
 
+RAISE INFO 'events_insert_commit_func';
+CREATE OR REPLACE FUNCTION events_insert_commit_func ()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  sdb_function_set_search_path
+  AS $$
+  BEGIN
+  -- Function for events on-commit insert trigger
+  --
+  -- AGPL_notice(`  --', `2026',
+                 `The Meme Factory, Inc., www.karlpinc.com')
+  --
+  -- Remarks:
+  --   Because this only checks for a required FOLLOW_OBSERVERS row,
+  -- and because the Behavior column cannot change, this is only
+  -- triggered on INSERT.
+  --
+  -- This check is done in the EVENTS trigger instead of the
+  -- ARRIVALS trigger because having no ARRIVALS row is allowed
+  -- (but generates a warning) the check must be done here.
+
+  IF NEW.behavior = 'sdb_arrival' THEN
+    DECLARE
+      -- ARRIVALS
+      a_seq arrivals.seq%TYPE;
+      a_neststart arrivals.neststart%TYPE;
+      a_nestend arrivals.nestend%TYPE;
+      a_cycle arrivals.cycle%TYPE;
+      a_datasource arrivals.datasource%TYPE;
+      -- WATCHES
+      a_wid watches.wid%TYPE;
+      a_animid watches.animid%TYPE;
+      a_date watches.date%TYPE;
+      -- ROLES
+      a_pid roles.pid%TYPE;
+      a_role roles.role%TYPE;
+      a_participant roles.participant%TYPE;
+
+    BEGIN
+      -- A related FOLLOW_OBSERVERS row must exist.
+      SELECT arrivals.seq, arrivals.neststart
+           , arrivals.nestend, arrivals.cycle, arrivals.datasource
+           , watches.wid, watches.animid, watches.date
+           , roles.pid, roles.role, roles.participant
+        INTO a_seq       , a_neststart
+           , a_nestend    , a_cycle     , a_datasource
+           , a_wid      , a_animid      , a_date
+           , a_pid    , a_role    , a_participant
+        FROM watches
+          LEFT OUTER JOIN arrivals ON (arrivals.eid = NEW.eid)
+          LEFT OUTER JOIN roles ON (roles.eid = NEW.eid)
+        WHERE watches.wid = NEW.wid
+              AND NOT EXISTS
+                (SELECT 1
+                   FROM follow_observers
+                   WHERE follow_observers.wid = NEW.wid)
+        ORDER BY roles.participant;
+
+      IF FOUND THEN
+        RAISE EXCEPTION integrity_constraint_violation USING
+              MESSAGE = 'Error on INSERT of EVENTS'
+            , DETAIL = 'Arrival events must have a related FOLLOW_OBSERVERS'
+                       || ' row, but there is no such row'
+                       || '): Key (EID) = ('
+                       || NEW.eid
+                       || '): Value (Behavior) = ('
+                       || NEW.behavior
+                       || '), Value (Start) = ('
+                       || NEW.start
+                       || '), Value (Stop) = ('
+                       || NEW.stop
+                       || '): Key (WATCHES.WID) = ('
+                       || a_wid
+                       || '), Value (WATCHES.AnimID) = ('
+                       || a_animid
+                       || '), Value (WATCHES.Date) = ('
+                       || a_date
+                       || ': Key (ARRIVALS.EID = ('
+                       || a_eid
+                       || '): Value (ARRIVALS.Seq) = ('
+                       || a_seq
+                       || '): Value (ARRIVALS.NestStart) = ('
+                       || a_neststart
+                       || '): Value (ARRIVALS.NestEnd) = ('
+                       || a_nestend
+                       || '): Value (ARRIVALS.Cycle) = ('
+                       || a_cycle
+                       || '): Value (ARRIVALS.datasource) = ('
+                       || a_datasource
+                       || '): Key (ROLES.PID) = ('
+                       || a_pid
+                       || '), Value (ROLES.Role) = ('
+                       || a_role
+                       || '), Value (ROLES.Participant) = ('
+                       || a_participant
+                       || ')';
+      END IF;
+    END;
+  END IF;
+
+  RETURN NULL;
+  END;
+$$;
+
+
 RAISE INFO 'events_delete_commit_func';
 CREATE OR REPLACE FUNCTION events_delete_commit_func ()
   RETURNS trigger
@@ -345,6 +450,14 @@ CREATE TRIGGER events_trigger
   ON events FOR EACH ROW
   EXECUTE PROCEDURE events_func();
 
+RAISE INFO 'events_insert_commit_trigger';
+CREATE CONSTRAINT TRIGGER events_insert_commit_trigger
+  AFTER INSERT
+  ON events
+  DEFERRABLE INITIALLY DEFERRED
+  FOR EACH ROW
+  Execute PROCEDURE events_insert_commit_func();
+
 RAISE INFO 'events_delete_commit_trigger';
 CREATE CONSTRAINT TRIGGER events_delete_commit_trigger
   AFTER DELETE
-- 
2.34.1